--1. 定义存储过程实现查询出账户余额最低的银行卡账户信息,显示银行卡号，姓名，账户余额
create proc proc_MinMoneyCard
as
begin
select top 1 CardNo 银行卡号,RealName 姓名,CardMoney 余额
from BankCard inner join AccountInfo on BankCard.AccountId=AccountInfo.AccountId
order by CardMoney asc
end
go

exec proc_MinMoneyCard

--2. 模拟银行卡存钱操作，传入银行卡号，存钱金额，实现存钱操作
create proc proc_CunQian
@CardNo varchar(30),@MoneyInBank money
as
update BankCard set CardMoney = CardMoney + @MoneyInBank where CardNo = @CardNo
insert into CardExchange (CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
	   values(@CardNo,@MoneyInBank,0,GETDATE())
go

exec proc_CunQian '420107199904054233',1000

--3. 模拟银行卡取钱操作，传入银行卡号，取钱金额，实现取钱操作，取钱成功，返回1，取钱失败返回-1
create proc proc_QuQian
@CardNo varchar(30),@MoneyOutBank money
as
update BankCard set CardMoney = CardMoney - @MoneyOutBank where CardNo = @CardNo
if @@ERROR <> 0
	return -1
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
	   values(@CardNo,0,@MoneyOutBank,GETDATE())
return 1
go

declare @returnvalue int
exec @returnvalue = proc_QuQian '420107199904054233',500
print @returnvalue


--4. **查询出某时间段的银行存取款信息以及存款总金额**，取款总金额，传入开始时间，结束时间，显示存取款交易信息的同时，返回存款总金额，取款总金额。
create proc proc_selectexchange
@StartTime varchar(20),--开始时间
@endTime varchar(20),--结束时间
@SumIn money output,--存款总金额
@SumOut money output --取款总金额
as 
select @SumIn = (select SUM(MoneyInBank) from CardExchange where ExchangeTime between 
@StartTime + '00:00:00' and @endTime + '23:59:59')
select @SumOut = (select SUM(MoneyOutBank) from CardExchange where ExchangeTime between 
@StartTime + '00:00:00' and @endTime + '23:59:59')
select * from CardExchange
where ExchangeTime between @StartTime + '00:00:00' and @endTime + '23:59:59'
go

declare @SumIn money --存款总金额
declare @SumOut money --存款总金额
exec proc_selectexchange,@SumIn output,@SumOut output
select @SumIn
select @SumOut

--5. **密码升级**，传入用户名和密码，如果用户名密码正确，并且密码长度<8，自动升级成8位密码


--(提示：随机生成 0-9 的整数： float(rand()*10))  rand():随机生成0.0-1.0的小数 float:向下取整）
create proc procPwdUpgrade
@cardno nvarchar(20),
@pwd nvarchar(20) output
as
	if not exists(select * from BankCard where CardNo=@cardno and CardPwd=@pwd)
		set @pwd = ''
	else
	begin
		if len(@pwd) < 8
		begin
			declare @len int = 8- len(@pwd)
			declare @i int = 1
			while @i <= @len
			begin
				
				set @pwd = @pwd + cast(FLOOR(RAND()*10) as varchar(1))
				set @i = @i+1
			end
			update BankCard set CardPwd = @pwd where CardNo=@cardno
		end
	end
go


declare @pwd nvarchar(20) = '123456'
exec procPwdUpgrade '420107199904054233',@pwd output
select @pwd